﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text; 
using System.Data.Common;
using System.Data.OleDb;

namespace DbSharp.Tools.DbConnectionBuilder.Core.Plugin
{
    /// <summary>
    /// Trouble shooting "Could not find installable ISAM"
    /// http://it.toolbox.com/blogs/sharepoint-blog/could-not-find-installable-isam-17062
    /// http://support.microsoft.com/kb/209805
    /// </summary>
    class ExcelOleConnectionPlugin : IDbConnectionPlugin
    {


        #region IDbConnectionPlugin Members
   
        public PluginNamePair PluginNamePair
        {
            get { return new PluginNamePair("Microsoft.Jet.OLEDB.4.0") { DisplayName = "Excel Xls Query Driver" }; }
        }


        public DbConnectionStringBuilder CreateConnectionStrBuilder(DbServerCredential credential)
        {
            //Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\1.xls;Extended Properties="Excel 8.0;HDR=YES;IMEX=1" 
            string connectionStrFmt = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""{1};HDR=YES;IMEX=1""";
            string connectionStr = string.Format(connectionStrFmt, credential.DataSource, credential.DbFileVersion.PropertyVersion);

            var builder = new OleDbConnectionStringBuilder(connectionStr);
            return builder;
        }

        public DbServerCredential CreateCredential(string connectionString)
        {
            //Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\1.xls;Extended Properties="Excel 8.0;HDR=YES" 
            DbServerCredential credential = new DbServerCredential();
            var listFileVersion = GetCandidateDbFileVersionPairs();
            foreach (var item in listFileVersion)
            {
                credential.ListCandidateDbFileVersionPair.Add(item);
            }

            credential.DataSourceEnabled = true;
            credential.DbFileVersionEnabled = true;
            credential.DbFileVersionMust = true;

            var builder = new OleDbConnectionStringBuilder(connectionString);
            object dataSource;
            if (builder.TryGetValue("Data Source", out dataSource))
            {
                credential.DataSource = dataSource.ToString();
            }

            object extendedProperties;
            if (builder.TryGetValue("Extended Properties", out extendedProperties))
            {
                string xproperty = extendedProperties.ToString().Trim().ToUpper();
                int index = -1;
                for (int i = 0; i < listFileVersion.Count; i++)
                {
                    if (xproperty.IndexOf(listFileVersion[i].PropertyVersion, StringComparison.OrdinalIgnoreCase) > -1)
                    {
                        index = i;
                        break;
                    }
                }
                if (index > -1)
                    credential.DbFileVersion = listFileVersion[index];
            }

            credential.AdditionalInfo = "";
            return credential;
        }

        public DbConnection CreateDbConnection(string connectionString)
        {
            return new OleDbConnection(connectionString);
        }

        public List<FileVersionPair> GetCandidateDbFileVersionPairs()
        {
            var list = new List<FileVersionPair>();
            list.Add(new FileVersionPair() { DisplayVersion = "Excel 2002", PropertyVersion = "Excel 8.0" });
            list.Add(new FileVersionPair() { DisplayVersion = "Excel 2000", PropertyVersion = "Excel 8.0" });
            list.Add(new FileVersionPair() { DisplayVersion = "Excel 97", PropertyVersion = "Excel 8.0" });
            list.Add(new FileVersionPair() { DisplayVersion = "Excel 95", PropertyVersion = "Excel 5.0" });
            list.Add(new FileVersionPair() { DisplayVersion = "Excel 5.0", PropertyVersion = "Excel 5.0" });
            list.Add(new FileVersionPair() { DisplayVersion = "Excel 4.0", PropertyVersion = "Excel 4.0" });
            list.Add(new FileVersionPair() { DisplayVersion = "Excel 3.0", PropertyVersion = "Excel 3.0" });
            return list;
        }

        
        #endregion
    }
}
